package cn.dengta.webapp.trade.dao;

import java.util.Date;
import java.util.List;

import cn.dengta.webapp.base.dao.BaseDao;
import cn.dengta.webapp.trade.dto.*;
import cn.dengta.webapp.trade.entity.UserStat;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.ResultSetType;
import org.apache.ibatis.session.ResultHandler;

/**
 * 用户多维度汇总统计表 Mapper 接口
 *
 * @author Ready
 * @since 2022-09-19
 */
public interface UserStatDao extends BaseDao<UserStat> {

	@Update("INSERT INTO dt_user_stat(id, gender, invite_user_id, add_time, reg_country, update_time)" +
			"SELECT id, gender, agent_id, add_time, reg_country, NOW(3) FROM dt_user WHERE id = #{userId}")
	int initForUser(@Param("userId") Long userId);

	@Update("UPDATE dt_user_stat SET gender = #{gender}  WHERE id = #{userId}")
	int initForGender(@Param("userId") Long userId, @Param("gender") Integer gender);

	@Update("UPDATE dt_user_stat SET invite_user_id = #{inviteUserId}  WHERE id = #{userId}")
	int initForInvite(@Param("userId") Long userId, @Param("inviteUserId") Long inviteUserId);

	@Update("UPDATE dt_user_stat SET recharge_sum = recharge_sum + #{payAmount}, recharge_times = recharge_times + 1, update_time = #{now} WHERE id = #{userId}")
	int addRechargeSum(UserStatChangeDTO dto);

	@Update("UPDATE dt_user_stat SET greet_times = greet_times + 1," +
			" update_time = #{now}" +
			" WHERE id = #{payUserId}")
	int addGreetCost(UserStatChangeDTO dto);

	@Update("UPDATE dt_user_stat SET income_sum = income_sum + #{income}," +
			" update_time = #{now}" +
			" WHERE id = #{incomeUserId}")
	int addIncome(UserStatChangeDTO dto);

	@Update("UPDATE dt_user_stat SET income_sum = income_sum + #{income}," +
			" msg_income_sum = msg_income_sum + #{income}," +
			" update_time = #{now}" +
			" WHERE id = #{incomeUserId}")
	int addMsgIncome(UserStatChangeDTO dto);

	@Update("UPDATE dt_user_stat SET video_cost_sum = video_cost_sum + #{coin}," +
			" video_time_sum = video_time_sum + #{callTime}," +
			" video_times = video_times + #{times}," +
			" update_time = #{now}" +
			" WHERE id = #{payUserId}")
	int addVideoCost(UserStatChangeDTO dto);

	@Update("UPDATE dt_user_stat SET audio_cost_sum = audio_cost_sum + #{coin}," +
			" audio_time_sum = audio_time_sum + #{callTime}," +
			" audio_times = audio_times + #{times}," +
			" update_time = #{now}" +
			" WHERE id = #{payUserId}")
	int addAudioCost(UserStatChangeDTO dto);

	@Update("UPDATE dt_user_stat SET msg_cost_sum = msg_cost_sum + #{coin}," +
			" update_time = #{now}" +
			" WHERE id = #{payUserId}")
	int addMsgCost(UserStatChangeDTO dto);

	@Update("UPDATE dt_user_stat SET income_sum = income_sum + #{income}," +
			" video_income_sum = video_income_sum + #{income}," +
			" video_time_sum = video_time_sum + #{callTime}," +
			" video_times = video_times + #{times}," +
			" update_time = #{now}" +
			" WHERE id = #{incomeUserId}")
	int addVideoIncome(UserStatChangeDTO dto);

	@Update("UPDATE dt_user_stat SET income_sum = income_sum + #{income}," +
			" audio_income_sum = audio_income_sum + #{income}," +
			" audio_time_sum = audio_time_sum + #{callTime}," +
			" audio_times = audio_times + #{times}," +
			" update_time = #{now}" +
			" WHERE id = #{incomeUserId}")
	int addAudioIncome(UserStatChangeDTO dto);

	@Update("UPDATE dt_user_stat SET gift_cost_sum = gift_cost_sum + #{coin}," +
			" update_time = #{now}" +
			" WHERE id = #{payUserId}")
	int addGiftCost(UserStatChangeDTO dto);

	@Update("UPDATE dt_user_stat SET income_sum = income_sum + #{income}," +
			" gift_income_sum = gift_income_sum + #{income}," +
			" update_time = #{now}" +
			" WHERE id = #{incomeUserId}")
	int addGiftIncome(UserStatChangeDTO dto);

	@Update("UPDATE dt_user_stat SET income_sum = income_sum + #{income}," +
			" update_time = #{now}" +
			" WHERE id = #{incomeUserId}")
	int addOtherIncome(UserStatChangeDTO dto);

	/**
	 * 为 历史用户 初始化 统计数据
	 */
	@Update("INSERT INTO dt_user_stat(id, gender, invite_user_id, add_time, reg_country, update_time)" +
			" SELECT id, gender, agent_id, add_time, reg_country, NOW(3) FROM dt_user WHERE id <= #{maxUserId}")
	int initForOldUsers(@Param("maxUserId") Long maxUserId);

	/**
	 * 为 历史用户 累加历史 充值金额、充值次数
	 */
	@Update("UPDATE dt_user_stat s," +
			" (SELECT user_id, SUM(real_pay) AS real_pay, COUNT(*) AS times" +
			" FROM tr_recharge_log" +
			" WHERE `status` = 2 AND id BETWEEN #{minId} AND #{maxId}" +
			" GROUP BY user_id) r" +
			" SET s.recharge_sum = s.recharge_sum + r.real_pay * 100, s.recharge_times = s.recharge_times + r.times" +
			" WHERE s.id = r.user_id")
	int initForOldRecharges(@Param("minId") Long minId, @Param("maxId") Long maxId);

	/**
	 * 为 历史用户 累加 搭讪次数
	 */
	@Update("UPDATE dt_user_stat s," +
			" (SELECT from_user_id, COUNT(*) AS times" +
			" FROM im_greet_log" +
			" WHERE id BETWEEN #{minId} AND #{maxId}" +
			" GROUP BY from_user_id ) l" +
			" SET s.greet_times = s.greet_times + l.times" +
			" WHERE l.from_user_id = s.id")
	int initForOldGreets(@Param("minId") Long minId, @Param("maxId") Long maxId);

	/**
	 * 为 历史用户 累加 礼物消耗、视频消耗, 语音消耗
	 */
	@Update("UPDATE dt_user_stat s," +
			" (SELECT user_id, SUM(IF(trade_type IN ('COIN_FOR_GIFT', 'COIN_FOR_GIFT_BAG'), ABS(trade_amount), 0)) AS gift_cost_sum," +
			" SUM(IF(trade_type IN ('COIN_FOR_VIDEO'), ABS(trade_amount), 0)) AS video_cost_sum," +
			" SUM(IF(trade_type IN ('COIN_FOR_AUDIO'), ABS(trade_amount), 0)) AS audio_cost_sum," +
			" SUM(IF(trade_type IN ('COIN_FOR_MSG'), ABS(trade_amount), 0)) AS msg_cost_sum" +
			" FROM tr_user_coin_log" +
			" WHERE trade_type IN ('COIN_FOR_GIFT', 'COIN_FOR_GIFT_BAG', 'COIN_FOR_VIDEO', 'COIN_FOR_AUDIO','COIN_FOR_MSG') AND id BETWEEN #{minId} AND #{maxId}" +
			" GROUP BY user_id ) l" +
			" SET s.gift_cost_sum = s.gift_cost_sum + l.gift_cost_sum," +
			" s.video_cost_sum = s.video_cost_sum + l.video_cost_sum," +
			" s.audio_cost_sum = s.audio_cost_sum + l.audio_cost_sum," +
			" s.msg_cost_sum = s.msg_cost_sum + l.msg_cost_sum" +
			" WHERE l.user_id = s.id")
	int initForGiftAndVideoCosts(@Param("minId") Long minId, @Param("maxId") Long maxId);

	/**
	 * 为 历史用户 初始化 可用金币余额
	 */
	@Update("UPDATE dt_user_stat s," +
			" tr_user_coin l" +
			" SET s.active_coin = l.active_amount" +
			" WHERE l.id = s.id AND s.id BETWEEN #{minId} AND #{maxId}")
	int initForOldCoins(@Param("minId") Long minId, @Param("maxId") Long maxId);

	/**
	 * 增量更新 可用金币余额
	 */
	@Update("UPDATE dt_user_stat s," +
			" tr_user_coin l" +
			" SET s.active_coin = l.active_amount, s.update_time = NOW(3)" +
			" WHERE l.id = s.id AND l.update_time >= #{minAddTime}")
	int updateCoins(@Param("minAddTime") Date minAddTime);

	/**
	 * 更新用户 可用金币余额
	 */
	@Update("UPDATE dt_user_stat s" +
			" SET s.active_coin = #{coin}, s.update_time = NOW(3)" +
			" WHERE s.id = #{userId}")
	int updateCoinsByUserId(@Param("userId") Long userId, @Param("coin") Integer coin);

	/**
	 * 为 历史用户 初始化 各种收益
	 */
	@Update("UPDATE dt_user_stat s," +
			" (" +
			" SELECT user_id, SUM(trade_amount) AS income_sum," +
			" SUM(IF(coin_trade_type IN ('COIN_FOR_GIFT', 'COIN_FOR_GIFT_BAG'), trade_amount, 0)) AS gift_income_sum," +
			" SUM(IF(coin_trade_type IN ('COIN_FOR_GREET', 'COIN_FOR_MSG', 'COIN_FOR_QUESTION'), trade_amount, 0)) AS msg_income_sum," +
			" SUM(IF(coin_trade_type IN ('COIN_FOR_VIDEO'), trade_amount, 0)) AS video_income_sum," +
			" SUM(IF(coin_trade_type IN ('COIN_FOR_AUDIO'), trade_amount, 0)) AS audio_income_sum" +
			" FROM tr_user_integral_log" +
			" WHERE coin_trade_type IN ('COIN_FOR_GREET', 'COIN_FOR_GIFT', 'COIN_FOR_GIFT_BAG', 'COIN_FOR_MSG', 'COIN_FOR_QUESTION', 'COIN_FOR_AUDIO', 'COIN_FOR_VIDEO') AND id BETWEEN #{minId} AND #{maxId}" +
			" GROUP BY user_id" +
			") l" +
			" SET s.income_sum = s.income_sum + l.income_sum * 100," +
			" s.gift_income_sum = s.gift_income_sum + l.gift_income_sum * 100," +
			" s.msg_income_sum = s.msg_income_sum + l.msg_income_sum * 100," +
			" s.video_income_sum = s.video_income_sum + l.video_income_sum * 100," +
			" s.audio_income_sum = s.audio_income_sum + l.audio_income_sum * 100" +
			" WHERE l.user_id = s.id")
	int initForIncomes(@Param("minId") Long minId, @Param("maxId") Long maxId);

	/**
	 * 为 历史的拨打用户 初始化 视频通话时间、次数
	 */
	@Update("UPDATE dt_user_stat s," +
			" (SELECT from_user_id AS user_id, SUM(duration) AS video_time_sum, COUNT(*) AS times" +
			" FROM im_media_log" +
			" WHERE type = 3 AND id BETWEEN #{minId} AND #{maxId} AND duration > 0" +
			" GROUP BY from_user_id) l" +
			" SET s.video_times = s.video_times + l.times," +
			" s.video_time_sum = s.video_time_sum + l.video_time_sum" +
			" WHERE l.user_id = s.id")
	int initVideoTimesForFromUser(@Param("minId") Long minId, @Param("maxId") Long maxId);

	/**
	 * 为 历史的接听用户 初始化 视频通话时间、次数
	 */
	@Update("UPDATE dt_user_stat s," +
			" (SELECT to_user_id AS user_id, SUM(duration) AS video_time_sum, COUNT(*) AS times" +
			" FROM im_media_log" +
			" WHERE type = 3 AND id BETWEEN #{minId} AND #{maxId} AND duration > 0" +
			" GROUP BY to_user_id) l" +
			" SET s.video_times = s.video_times + l.times," +
			" s.video_time_sum = s.video_time_sum + l.video_time_sum" +
			" WHERE l.user_id = s.id")
	int initVideoTimesForToUser(@Param("minId") Long minId, @Param("maxId") Long maxId);

	/**
	 * 更新 所属推广员
	 */
	@Update("UPDATE dt_user_stat" +
			" SET invite_user_id = #{newTopInviteUserId}" +
			" WHERE invite_user_id = #{oldTopInviteUserId}")
	int replaceTopInviteUserId(@Param("newTopInviteUserId") Long newTopInviteUserId, @Param("oldTopInviteUserId") Long oldTopInviteUserId);

	@Select("SELECT id, gender FROM dt_user_stat ${ew.customSqlSegment}")
	@Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 1000)
	@ResultType(UserStat.class)
	void scanLevelMatched(@Param("ew") QueryWrapper<UserStat> wrapper, ResultHandler<UserStat> handler);

	@Select("SELECT ${ew.sqlSelect} FROM dt_user_stat ${ew.customSqlSegment}")
	@Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 1000)
	@ResultType(UserStat.class)
	void scanFields(@Param("ew") QueryWrapper<UserStat> wrapper, ResultHandler<UserStat> handler);

	@Select("SELECT l.from_user_id, u.gender, IF(l.from_user_id = l.low_user_id, l.high_user_id, l.low_user_id) AS to_user_id, l.status FROM im_greet_log l" +
			" JOIN dt_user u ON l.from_user_id = u.id" +
			" WHERE l.add_time >= #{minAddTime} AND u.gender >= 0 ORDER BY l.id DESC")
	@Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 1000)
	@ResultType(GreetDTO.class)
	void scanGreetLogs(@Param("minAddTime") Date minAddTime, ResultHandler<GreetDTO> handler);

	@Select("SELECT igl.from_user_id, du.gender, IF(from_user_id = low_user_id, high_user_id, low_user_id) AS to_user_id, igl.status" +
			" FROM im_greet_log igl JOIN dt_user du ON igl.from_user_id = du.id" +
			" WHERE igl.add_time >= #{minAddTime} AND igl.update_time > #{minUpdateTime} AND du.gender >= 0" +
			" ORDER BY igl.id DESC")
	@Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 1000)
	@ResultType(GreetDTO.class)
	void scanUpdateGreetLogs(@Param("minAddTime") Date minAddTime, @Param("minUpdateTime") Date minUpdateTime, ResultHandler<GreetDTO> handler);

	@Select("SELECT IFNULL(SUM(status) * 100 / COUNT(*),0) AS replyRate,COUNT(*) AS greetNum" +
			" FROM (SELECT status FROM im_greet_log igl" +
			" WHERE from_user_id != #{userId} AND (low_user_id = #{userId} OR high_user_id = #{userId})" +
			" AND add_time > #{minUpdateTime}) t")
	ReplyRateDTO selectGreetReplyRate(@Param("userId") Long userId, @Param("minUpdateTime") Date minUpdateTime);

	@Select("SELECT IFNULL(SUM(status) * 100 / COUNT(*), 0)  AS replyRate, COUNT(*) AS greetNum" +
			" FROM (SELECT status FROM im_greet_log igl" +
			" WHERE from_user_id = #{userId} " +
			" AND add_time > #{minUpdateTime}) t")
	ReplyRateDTO selectToGreetReplyRate(@Param("userId") Long userId, @Param("minUpdateTime") Date minUpdateTime);

	@Select("SELECT id, rec_level FROM dt_user WHERE rec_level > 0 AND gender = 0 ORDER BY id DESC")
	@Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 1000)
	@ResultType(UserRecLevelDTO.class)
	void scanRecLevels(ResultHandler<UserRecLevelDTO> handler);

	@Select("SELECT IF(low_user_id = #{userId}, high_user_id,low_user_id) as user_id FROM im_greet_log WHERE (low_user_id = #{userId} OR high_user_id = #{userId} ) LIMIT 500")
	List<Long> selectGreetLogs(@Param("userId") Long userId);

	@Update("UPDATE im_user_pull_wires SET gender = #{gender}, amount = 0 WHERE id = #{userId}")
	int updatePullWireGender(@Param("userId") Long userId, @Param("gender") Integer gender);

	/** 更新登录天数 */
	@Update("UPDATE dt_user_stat us SET us.login_day_count = #{loginCount}, us.login_times = #{loginTimes} WHERE us.id = #{userId}")
	int updateUserStatByLoginDayCount(@Param("userId") Long userId, @Param("loginCount") Integer loginCount, @Param("loginTimes") Integer loginTimes);

}
